using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace OrderingSystem.Infrastructure.Data
{
    public static class DbMigrationHelper
    {
        /// <summary>
        /// 验证数据库结构与实体模型的一致性
        /// </summary>
        /// <param name="context">数据库上下文</param>
        /// <param name="logger">日志记录器</param>
        /// <returns>验证结果，包含不一致的实体和属性信息</returns>
        public static async Task<Dictionary<string, List<string>>> ValidateModelMappings(
            ApplicationDbContext context, 
            ILogger logger)
        {
            var inconsistencies = new Dictionary<string, List<string>>();
            
            try
            {
                logger.LogInformation("开始验证实体映射与数据库结构的一致性...");
                
                // 获取所有实体类型
                var entityTypes = context.Model.GetEntityTypes().ToList();
                
                foreach (var entityType in entityTypes)
                {
                    var tableName = entityType.GetTableName();
                    var entityName = entityType.ClrType.Name;
                    
                    logger.LogInformation($"验证实体 {entityName} (表名: {tableName})");
                    
                    // 检查表是否存在
                    var tableExists = await TableExists(context, tableName);
                    if (!tableExists)
                    {
                        logger.LogWarning($"表 {tableName} 不存在");
                        if (!inconsistencies.ContainsKey(entityName))
                        {
                            inconsistencies[entityName] = new List<string>();
                        }
                        inconsistencies[entityName].Add($"表 {tableName} 不存在");
                        continue;
                    }
                    
                    // 获取表的列信息
                    var columns = await GetTableColumns(context, tableName);
                    
                    // 验证每个属性的映射
                    foreach (var property in entityType.GetProperties())
                    {
                        var columnName = property.GetColumnName();
                        if (columnName != null && !columns.Contains(columnName.ToLower()))
                        {
                            logger.LogWarning($"实体 {entityName} 的属性 {property.Name} 映射到不存在的列 {columnName}");
                            
                            if (!inconsistencies.ContainsKey(entityName))
                            {
                                inconsistencies[entityName] = new List<string>();
                            }
                            inconsistencies[entityName].Add($"属性 {property.Name} 映射到不存在的列 {columnName}");
                        }
                    }
                }
                
                logger.LogInformation("实体映射验证完成");
                
                if (inconsistencies.Count == 0)
                {
                    logger.LogInformation("所有实体映射与数据库结构一致");
                }
                else
                {
                    logger.LogWarning($"发现 {inconsistencies.Count} 个实体存在映射不一致问题");
                }
            }
            catch (Exception ex)
            {
                logger.LogError(ex, "验证实体映射时发生错误");
            }
            
            return inconsistencies;
        }
        
        /// <summary>
        /// 检查表是否存在
        /// </summary>
        private static async Task<bool> TableExists(ApplicationDbContext context, string tableName)
        {
            // 修改为使用正确的方式检查表是否存在
            var sql = $"SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = '{tableName}'";
            
            using (var command = context.Database.GetDbConnection().CreateCommand())
            {
                command.CommandText = sql;
                
                if (command.Connection.State != System.Data.ConnectionState.Open)
                {
                    await command.Connection.OpenAsync();
                }
                
                var result = await command.ExecuteScalarAsync();
                return Convert.ToInt32(result) > 0;
            }
        }
        
        /// <summary>
        /// 获取表的所有列名
        /// </summary>
        private static async Task<List<string>> GetTableColumns(ApplicationDbContext context, string tableName)
        {
            var columns = new List<string>();
            
            try
            {
                // 使用原生SQL查询获取表的列信息
                var sql = $"SELECT column_name FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = '{tableName}'";
                
                using (var command = context.Database.GetDbConnection().CreateCommand())
                {
                    command.CommandText = sql;
                    
                    if (command.Connection.State != System.Data.ConnectionState.Open)
                    {
                        await command.Connection.OpenAsync();
                    }
                    
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var columnName = reader.GetString(0).ToLower();
                            columns.Add(columnName);
                        }
                    }
                }
            }
            catch (Exception)
            {
                // 如果查询失败，返回空列表
                return new List<string>();
            }
            
            return columns;
        }
    }
} 